SET NOCOUNT OFF
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF
GO

/******************************************************************************/
/* Step 1. Remove stored procedure if it already exists                       */
/******************************************************************************/
IF (SELECT OBJECT_ID('[CaseManagement].[SaveVisit]')) IS NOT NULL
BEGIN
  DROP PROCEDURE [CaseManagement].[SaveVisit]
END

GO

/*****************************************************************************/
/*****************************************************************************/
CREATE PROCEDURE [CaseManagement].[SaveVisit]
@VisitOID                       bigint          = NULL,
@VisitVersion                   rowversion      = NULL,
@CaseOID                        bigint,
@VisitArrivalDate               datetime        = NULL,
@VisitDischargeDate             datetime        = NULL,
@VisitFacilityID                bigint          = NULL,
@VisitFamilyPresent             bit,
@VisitFamilyArrivalDate         datetime        = NULL,
@VisitFamilyDepartureDate       datetime        = NULL,
@VisitMethodOfTravel            varchar(128)    = NULL,
@VisitOriginOfTravel            varchar(128)    = NULL,
@VisitInvitationalTravelOrders  bit

AS  

DECLARE @RetCode int,
        @RowCount int

-- 1. If new record (OID is not set)
IF ISNULL(@VisitOID, 0) <= 0 
BEGIN
	-- 1.1. Perform insert (OID is identity).
	INSERT INTO dbo.Visits
	(CaseID, ArrivalDate, DischargeDate, FacilityID, FamilyPresent, FamilyArrivalDate, FamilyDepartureDate, MethodOfTravel, OriginOfTravel, InvitationalTravelOrders, UpdatedDate, CreatedDate)
	VALUES
	(@CaseOID, @VisitArrivalDate, @VisitDischargeDate, @VisitFacilityID, @VisitFamilyPresent, @VisitFamilyArrivalDate, @VisitFamilyDepartureDate, @VisitMethodOfTravel, @VisitOriginOfTravel, @VisitInvitationalTravelOrders, GETDATE(), GETDATE())

	-- 1.2. Determine OID of newly inserted object
	SET @VisitOID = SCOPE_IDENTITY()
END

-- 2. If OID is set (existing record)
ELSE
BEGIN
	-- 2.1. Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.Visits
	SET CaseID = @CaseOID
	    ,ArrivalDate = @VisitArrivalDate
	    ,DischargeDate = @VisitDischargeDate
	    ,FacilityID = @VisitFacilityID
	    ,FamilyPresent = @VisitFamilyPresent
	    ,FamilyArrivalDate = @VisitFamilyArrivalDate
	    ,FamilyDepartureDate = @VisitFamilyDepartureDate
	    ,MethodOfTravel = @VisitMethodOfTravel
	    ,OriginOfTravel = @VisitOriginOfTravel
	    ,InvitationalTravelOrders = @VisitInvitationalTravelOrders
		,UpdatedDate = GETDATE()
	WHERE ((OID = @VisitOID) AND (Version = @VisitVersion))
    SELECT @RetCode = @@ERROR, @RowCount = @@ROWCOUNT
    IF (@RetCode <> 0) GOTO ExitError

	-- 2.2. If no records were found (and updated) - it means that
	-- another process had updated this record (therefore timestamp will be different)
	-- or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @RowCount = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'Visit' ) --WITH LOG
		GOTO ExitError
	END
END 

---------------------------------------------------------------------------------------------------
--	Return the values to the caller
---------------------------------------------------------------------------------------------------
SELECT OID AS 'VisitOID', Version AS 'VisitVersion'
FROM dbo.Visits
WHERE OID = @VisitOID

RETURN(0)

ExitError:
	RETURN(1)
GO

/******************************************************************************/
/* Step 3. Assign appropriate permissions                                     */
/******************************************************************************/
GRANT EXECUTE ON [CaseManagement].[SaveVisit] TO [Liberty_Users]
GO
-- Developers and Admins have permissions to execute through membership   
